﻿using log4net;
using MySql.Data.MySqlClient;
using System;
using System.Data;

namespace 语文字帖
{
    /// <summary>
    /// 数据库帮助类
    /// </summary>
    public static class MysqlHelper
    {
        private static readonly ILog log = LogManager.GetLogger("NETCoreRepository", typeof(string));

        /// <summary>
        /// 数据库的连接串
        /// </summary>
        public static string ConnectionString { get; set; } // => "server=127.0.0.1;userid=root;password=123456;database=19road;charset=UTF8";

        public static MySqlConnection GetConnection()
        {
            if (!string.IsNullOrEmpty(ConnectionString))
            {
                return new MySqlConnection(ConnectionString);
            }
            else
            {
                return null;
            }
        }

        private static string RemoveInvalidChar(string text)
        {
            if (!string.IsNullOrEmpty(text))
            {
                return text.Replace("'", string.Empty).Replace("\"", string.Empty).Replace("\n", string.Empty).Replace("\t", string.Empty).Replace("\r", string.Empty);
            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// 根据姓名查询成绩
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static DataSet QueryByName(string name)
        {
            string cmdtext = "SELECT name, matchbegindate, matchname, levelname, ranking, organization \n"
                                + "FROM matchresultinfo \n"
                                + "WHERE name = ?name ORDER BY matchbegindate DESC";
            MySqlConnection conn = GetConnection();
            log.DebugFormat("查询“{0}”的国际象棋成绩", name);
            if (conn != null)
            {
                try
                {
                    conn.Open();
                    try
                    {
                        try
                        {
                            DataSet ds = new DataSet();
                            MySqlCommand cmd = new MySqlCommand(cmdtext, conn);
                            MySqlDataAdapter apt = new MySqlDataAdapter(cmd);
                            cmd.Parameters.Add(new MySqlParameter("name", name));
                            apt.Fill(ds);
                            return ds;
                        }
                        catch (Exception e)
                        {
                            log.Error("查询报错", e);
                            throw e;
                        }
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
                catch (Exception connExcep)
                {
                    log.Error("数据库连接报错", connExcep);
                    throw connExcep;
                }
            }
            else
            {
                throw new Exception();
            }
        }
    }
}
